<?php
class M_attendance extends MY_Model
{
	//==================== Attendance getters for Adviser ============================
	/*
		gets all attendance of a certain room
	*/
	public function get_section_attendance($room_id)
	{
		$sql = "SELECT * FROM attendance 
				WHERE attendance.room_id = ?
				AND is_subject_att = '0'
				GROUP BY att_date";
		$query = $this->db->query($sql, array($room_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	/*
		gets attendance of certain date (checker)
	*/
	public function get_section_attendance_per_date($room_id, $date)
	{
		$sql = "SELECT * FROM attendance 
				JOIN profiles ON attendance.profile_id = profiles.profile_id
				WHERE attendance.room_id = ? 
				AND att_date = ?
				AND is_subject_att ='0'";
		$query = $this->db->query($sql, array($room_id, $date));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_students_in_room($room_id)
	{
		$sql = "SELECT * FROM rooms
				JOIN sections ON rooms.section_id = sections.id
				JOIN enrollments on sections.id = enrollments.e_section_id
				JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
				WHERE rooms.room_id = ?";
		$query = $this->db->query($sql, array($room_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	//======================== END ===========================
	
	//========================= Attendance getters for Subject teacher ===============
	
	
	public function get_subject_attendance($subj_id)
	{
		$sql = "SELECT attendance.att_date,
				SUM( IF( attendance.in_class =0, 1, 0 ) ) AS a,
				SUM( IF( attendance.in_class =1, 1, 0 ) ) AS p, 
				SUM( IF( attendance.in_class =2, 1, 0 ) ) AS t, 
				SUM( IF( attendance.in_class =3, 1, 0 ) ) AS c,
						attendance.reason, 
						attendance.remarks, 
						profiles.lastname, 
						profiles.firstname, 
						profiles.middlename, 
						attendance.profile_id, 
						attendance.section_id, 
						attendance.room_id
				FROM attendance
				JOIN profiles ON attendance.profile_id = profiles.profile_id
				WHERE attendance.subject_id = ?
				AND is_subject_att =  '1'
				GROUP BY (att_date)";
		$query = $this->db->query($sql, array($subj_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_subject_attendance_per_month($subj_id)
	{
		$sql = "SELECT attendance.att_date FROM attendance WHERE attendance.subject_id = ? GROUP BY MONTH(att_date)";
		$query = $this->db->query($sql, array($subj_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_subject_attendance_per_date($subj_id, $date)
	{
		$sql = "SELECT attendance.in_class,
						attendance.reason, 
						attendance.remarks, 
						profiles.lastname, 
						profiles.firstname, 
						profiles.middlename, 
						attendance.profile_id, 
						attendance.section_id, 
						attendance.room_id
				FROM attendance
				JOIN profiles ON attendance.profile_id = profiles.profile_id
				WHERE attendance.subject_id = ?
				AND att_date = ?
				AND is_subject_att =  '1'";
		$query = $this->db->query($sql, array($subj_id, $date));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}

	public function get_students_in_subject($subj_id)
	{
		$sql = "SELECT profiles.lastname, 
						profiles.firstname, 
						profiles.middlename, 
						enrollments.e_profile_id,
						rooms.room_id
				FROM subjects
				JOIN schedule ON subjects.subj_id = schedule.subject_id
				JOIN rooms ON schedule.section_id = rooms.section_id
				JOIN sections ON schedule.section_id = sections.id
				JOIN enrollments ON sections.id = enrollments.e_section_id
				JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
				WHERE subjects.subj_id = ?";
		$query = $this->db->query($sql, array($subj_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	//======================== END ===========================
	
	
	function student_attendance($stud_id)
	{
		/*in_class column: 0 = absent AS a, 1=present AS p, 2=tardy AS t, 3=cut class AS c*/
		$sql = "SELECT attendance.profile_id, attendance.att_date, 
				SUM( IF( attendance.in_class =0, 1, 0 ) ) AS a,
				SUM( IF( attendance.in_class =1, 1, 0 ) ) AS p, 
				SUM( IF( attendance.in_class =2, 1, 0 ) ) AS t, 
				SUM( IF( attendance.in_class =3, 1, 0 ) ) AS c
				FROM enrollments
				JOIN attendance ON e_profile_id = profile_id
				WHERE e_id = ?
				GROUP BY MONTH( att_date )";
		$query = $this->db->query($sql, array($stud_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	function student_attendance_by_month($stud_id, $month_year)
	{
		$m_yr_arr = explode("-", $month_year);
		$month = $m_yr_arr[0];
		$year = $m_yr_arr[1];
		/*in_class column: 0 = absent, 1=present, 2=tardy, 3=cut class*/
		$sql = "SELECT attendance.profile_id, attendance.att_date, 
				IF( attendance.in_class =0, '*', '' ) AS a, 
				IF( attendance.in_class =1, '*', '' ) AS p, 
				IF( attendance.in_class =2, '*', '' ) AS t, 
				IF( attendance.in_class =3, '*', '' ) AS c
				FROM enrollments
				JOIN attendance ON e_profile_id = profile_id
				WHERE e_id = ?
				AND MONTH(att_date) = ?
				AND YEAR(att_date) = ?
				GROUP BY  att_date ";
		$query = $this->db->query($sql, array($stud_id,$month,$year));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	function get_attendance_totals($stud_id)
	{
		$sql = "SELECT 
				SUM( IF( attendance.in_class =0, 1, 0 ) ) AS a, 
				SUM( IF( attendance.in_class =1, 1, 0 ) ) AS p, 
				SUM( IF( attendance.in_class =2, 1, 0 ) ) AS t, 
				SUM( IF( attendance.in_class =3, 1, 0 ) ) AS c
				FROM enrollments
				JOIN attendance ON e_profile_id = profile_id
				WHERE e_id = ?";
		$query = $this->db->query($sql, array($stud_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	function get_attendance_totals_by_month($stud_id, $month_year)
	{
		$m_yr_arr = explode("-", $month_year);
		$month = $m_yr_arr[0];
		$year = $m_yr_arr[1];
		$sql = "SELECT 
				SUM( IF( attendance.in_class =0, 1, 0 ) ) AS a, 
				SUM( IF( attendance.in_class =1, 1, 0 ) ) AS p, 
				SUM( IF( attendance.in_class =2, 1, 0 ) ) AS t, 
				SUM( IF( attendance.in_class =3, 1, 0 ) ) AS c
				FROM enrollments
				JOIN attendance ON e_profile_id = profile_id
				WHERE e_id = ?
				AND MONTH(att_date) = ?
				AND YEAR(att_date) = ?";
		$query = $this->db->query($sql, array($stud_id, $month, $year));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_attendance_summary($enrollment_id)
	{
		$sy = $this->c_sy;
		$sql = "SELECT MONTH(att_date) as month,
				SUM( IF( attendance.in_class =1, 1, 0 ) ) AS p
				FROM enrollments
				JOIN attendance ON e_profile_id = profile_id
				WHERE e_id = ?
				AND 
				(YEAR(att_date) = ? OR YEAR(att_date) = ?)
                GROUP BY MONTH(att_date)
				";
		$query = $this->db->query($sql, array($enrollment_id, $sy->sy_from, $sy->sy_to));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function insert_attendance($data_arr)
	{
		$this->db->insert('attendance',$data_arr);
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
		
	public function update_attendance($data_arr, $id, $date)
	{
		$this->db->update('attendance', $data_arr, array('profile_id' => $id, 'att_date' => $date, 'is_subject_att' => '0'));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function update_subject_attendance($data_arr, $id, $date, $subj_id)
	{
		$this->db->update('attendance', $data_arr, array('profile_id' => $id, 'att_date' => $date, 'is_subject_att' => '1', 'subject_id' => $subj_id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function delete_attendance($room_id, $date)
	{
		$sql = "DELETE FROM attendance 
				WHERE att_date = ?
				AND room_id = ?";
		$query = $this->db->query($sql, array($date, $room_id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function delete_subject_attendance($date, $subj_id)
	{
		$sql = "DELETE FROM attendance 
				WHERE att_date = ?
				AND subject_id = ?";
		$query = $this->db->query($sql, array($date, $subj_id));
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
}

?>